package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.StringUtil;
import com.tianqu.tms.dto.MemberDto;
import com.tianqu.tms.dto.MemberRentalLocationInfo;

public class MemberDaoImp extends ViewListDao implements MemberDao {

	protected final String[] sortKeyList = new String[] { "MEMBERID", "REALNAME", "LEGAL", "PHONE", "CONTACTOWNER",
			"CONTACTPHONE", "INSERTTIME", "MEMSTATUS" };// 会员列表

	protected MemberDaoImp(int pageNo, int maxIndex, int sortKey, boolean asc) throws TmsDaoException {
		super(pageNo, maxIndex, sortKey, asc);
	}

	public MemberDaoImp() throws TmsDaoException {
		super();
	}

	/**
	 * 查询出所有会员信息
	 * 
	 * @throws TmsDaoException
	 */
	@Override
	public List findAllMemberInfo() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		MemberDto dto = null;
		List list = new ArrayList();

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("a.MEMBERID   MEMBERID	,");
			sql.append("a.REALNAME  REALNAME   ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" as a,");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" as b ");
			sql.append("where ");
			sql.append("a.userId = b.userId AND b.flag=1");
			sql.append(" ORDER BY a.REALNAME DESC ");

			ps = conn.prepareStatement(sql.toString());

			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new MemberDto();
				dto.setMemId(rs.getInt("MEMBERID"));
				dto.setMemName(rs.getString("REALNAME"));

				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return list;
	}

	@Override
	public List findAllMemberInfoM() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		MemberDto dto = null;
		List list = new ArrayList();

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("MEMBERID	,");
			sql.append("SHORTNAME	,");
			sql.append("REALNAME   ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" ORDER BY REALNAME DESC ");

			ps = conn.prepareStatement(sql.toString());

			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new MemberDto();
				dto.setMemId(rs.getInt("MEMBERID"));
				dto.setMemName(rs.getString("REALNAME"));
				dto.setShortName(rs.getString("SHORTNAME"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return list;
	}

	/**
	 * 
	 * 向会员表中添加一条信息
	 * 
	 * @throws TmsDaoException
	 */
	@Override
	public void insetMemberInfoByDto(MemberDto dto) throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("INSERT INTO ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append("(");
			sql.append("MEMBERID, ");// 会员ID
			sql.append("REALNAME, ");
			sql.append("SHORTNAME, ");
			sql.append("REALNAMEPY, ");
			sql.append("BUSINESSNO, ");
			sql.append("ACCESSCARDID, ");
			sql.append("ACCESSCARDNO, ");
			sql.append("ADRRESS, ");
			sql.append("CONTACTOWNER, ");
			sql.append("CONTACTPHONE, ");
			sql.append("LEGAL, ");
			sql.append("PHONE, ");
			sql.append("REGISTEREDCAPITAL, ");
			sql.append("CARDNO, ");
			sql.append("SETTLEPHONE, ");
			sql.append("ORGNO, ");
			sql.append("CARDPHOTO, ");
			sql.append("ORGPHOTO, ");
			sql.append("BUSINESSPHOTO, ");
			sql.append("MEMBERZIPCODE, ");
			sql.append("CARPORTINSTR, ");
			sql.append("CARPORT, ");
			sql.append("USEDCARPORT, ");
			sql.append("ANNUALTRANSACTIONVOLUME, ");
			sql.append("FRAGILESTICKERFREEPORT, ");
			sql.append("RECOMMEND, ");
			sql.append("AUCMARGIN, ");
			sql.append("MARGIN, ");
			sql.append("VIRTUALMARGIN, ");
			sql.append("REALMARGIN, ");
			sql.append("GIFTMARGIN, ");
			sql.append("CARDSCORE, ");
			sql.append("INTEGRALRATIO, ");
			sql.append("USERID, ");
			sql.append("UPDATEUSERID, ");
			sql.append("INSERTUSERID, ");
			sql.append("UPDATETIME, ");
			sql.append("INSERTTIME, ");
			sql.append("FLOORS  ");
			sql.append("  ) VALUES ( ");
			sql.append(
					" ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,SYSDATE(),SYSDATE(),? )");

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getMemName());
			ps.setString(2, dto.getRealName());
			ps.setString(3, dto.getShortName());
			ps.setString(4, dto.getRealNamePy());
			ps.setString(5, dto.getBusinessNo());
			ps.setString(6, dto.getAccessCardId());
			ps.setString(7, dto.getAccessCardNo());
			ps.setString(8, dto.getAdrress());
			ps.setString(9, dto.getContactOwner());
			ps.setString(10, dto.getContactPhone());
			ps.setString(11, dto.getLegal());
			ps.setString(12, dto.getPhone());
			ps.setInt(13, dto.getRegisteredCapital());
			ps.setString(14, dto.getCardNo());
			ps.setString(15, dto.getSettlePhone());
			ps.setString(16, dto.getOrgNo());
			ps.setString(17, dto.getCardPhoto());
			ps.setString(18, dto.getOrgPhoto());
			ps.setString(19, dto.getBusinessPhoto());
			ps.setString(20, dto.getMemberZipCode());
			ps.setString(21, dto.getCarPortInstr());
			ps.setInt(22, dto.getCarPort());// 车位数
			ps.setInt(23, dto.getUsedCarPort());
			ps.setInt(24, dto.getAnnualTransactionVolume());
			ps.setInt(25, dto.getFragileStickerFreePort());
			ps.setInt(26, (StringUtil.isNullOrBlank(dto.getRecommend())) ? 1 : Integer.valueOf(dto.getRecommend()));
			ps.setInt(27, dto.getAucMargin());
			ps.setInt(28, dto.getMargin());
			ps.setInt(29, dto.getVirtualMargin());
			ps.setInt(30, dto.getRealMargin());
			ps.setInt(31, dto.getGiftMargin());
			ps.setInt(32, dto.getCardScore());
			ps.setDouble(33, dto.getIntegralRatio());
			ps.setString(34, dto.getUserId());
			ps.setInt(35, dto.getUpdateUserId());
			ps.setInt(36, dto.getInsertUserId());
			ps.setString(37, dto.getFloors());

			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 获取最大会员ID
	 * 
	 * @throws TmsDaoException
	 */
	@Override
	public int getMaxMemberId() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int memberId = 0;
		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("MAX(MEMBERID) AS MEMID ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_MEMBER);

			ps = conn.prepareStatement(sql.toString());

			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				memberId = rs.getInt("MEMID");
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return memberId;
	}

	// huangwei st2
	@Override
	public List<MemberDto> findMemberList(String namePy) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		MemberDto dto = null;
		List<MemberDto> list = new ArrayList<MemberDto>();

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("MEMBER.MEMBERID  AS MEMBERID 	,");
			sql.append("MEMBER.REALNAME  AS REALNAME 	,");
			sql.append("MEMBER.USERID AS USERID,     ");
			sql.append("MEMBER.ADRRESS AS ADRRESS,     ");
			sql.append("MEMBER.CONTACTOWNER AS CONTACTOWNER,     ");
			sql.append("MEMBER.CONTACTPHONE AS CONTACTPHONE,     ");
			sql.append("MEMBER.MARGIN  AS MARGIN ,   ");
			sql.append("MEMBER.VIRTUALMARGIN AS VIRTUALMARGIN,     ");
			sql.append("TMS_USER.FLAG as MEMSTATUS     ");// 与user表关联取出帐号状态
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" AS MEMBER    ");
			sql.append(" LEFT JOIN    ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" AS TMS_USER      ");
			sql.append(" ON TMS_USER.USERID = MEMBER.USERID    ");
			sql.append(" WHERE TMS_USER.FLAG<>2 AND  MEMBER.REALNAMEPY LIKE '%" + namePy + "%'");
			// 排序方法
			sql.append(getOrderBySql(sortKeyList));
			ps = conn.prepareStatement(sql.toString());
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new MemberDto();
				dto.setMemId(rs.getInt("MEMBERID"));
				dto.setRealName(rs.getString("REALNAME"));
				dto.setUserId(rs.getString("USERID"));
				dto.setAdrress(rs.getString("ADRRESS"));
				dto.setContactOwner(rs.getString("CONTACTOWNER"));
				dto.setContactPhone(rs.getString("CONTACTPHONE"));
				dto.setMargin(rs.getInt("MARGIN"));
				dto.setVirtualMargin(rs.getInt("VIRTUALMARGIN"));
				dto.setMemStatus(rs.getInt("MEMSTATUS"));

				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return list;
	}

	/**
	 * 
	 * 会员信息修改删除列表页list获取
	 * 
	 * @throws TmsDaoException
	 */
	@Override
	public List<MemberDto> findMemberList() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		MemberDto dto = null;
		List<MemberDto> list = new ArrayList<MemberDto>();

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("MEMBER.MEMBERID  AS MEMBERID 	,");
			sql.append("MEMBER.REALNAME  AS REALNAME 	,");
			sql.append("MEMBER.USERID AS USERID,     ");
			sql.append("MEMBER.ADRRESS AS ADRRESS,     ");
			sql.append("MEMBER.CONTACTOWNER AS CONTACTOWNER,     ");
			sql.append("MEMBER.CONTACTPHONE AS CONTACTPHONE,     ");
			sql.append("MEMBER.MARGIN  AS MARGIN ,   ");
			sql.append("MEMBER.VIRTUALMARGIN AS VIRTUALMARGIN,     ");
			sql.append("USER.FLAG as MEMSTATUS,     ");// 与user表关联取出帐号状态
			sql.append("MEMBER.CARDSCORE     ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" AS MEMBER    ");
			sql.append(" LEFT JOIN    ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" AS USER      ");
			sql.append(" ON USER.USERID = MEMBER.USERID    ");
			sql.append(" WHERE USER.FLAG<>2  ");
			// 排序方法
			sql.append(getOrderBySql(sortKeyList));

			ps = conn.prepareStatement(sql.toString());

			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new MemberDto();
				dto.setMemId(rs.getInt("MEMBERID"));
				dto.setRealName(rs.getString("REALNAME"));
				dto.setUserId(rs.getString("USERID"));
				dto.setAdrress(rs.getString("ADRRESS"));
				dto.setContactOwner(rs.getString("CONTACTOWNER"));
				dto.setContactPhone(rs.getString("CONTACTPHONE"));
				dto.setMargin(rs.getInt("MARGIN"));
				dto.setVirtualMargin(rs.getInt("VIRTUALMARGIN"));
				dto.setMemStatus(rs.getInt("MEMSTATUS"));
				dto.setCardScore(rs.getInt("CARDSCORE"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return list;
	}

	/**
	 * 根据会员ID查询允许库存登记数量
	 */
	@Override
	public int findAnnualTransactionVolume(String memberId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int annualTransactionVolume = 0;

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ANNUALTRANSACTIONVOLUME");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" WHERE ");
			sql.append("MEMBERID=? ");

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, memberId);
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				annualTransactionVolume = rs.getInt("ANNUALTRANSACTIONVOLUME");
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return annualTransactionVolume;
	}

	/**
	 * 
	 * 商户查询/修改列表数据List获取
	 * 
	 * @throws TmsDaoException
	 */
	@Override
	public List<MemberDto> findMemberInfoList(String memberId, String flag) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		MemberDto dto = null;
		List<MemberDto> list = new ArrayList<MemberDto>();

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("MEMBER.MEMBERID  AS MEMBERID, ");
			sql.append("MEMBER.SHORTNAME  AS SHORTNAME, ");
			sql.append("MEMBER.LEGAL AS LEGAL, ");
			sql.append("MEMBER.PHONE AS PHONE, ");
			sql.append("MEMBER.CONTACTOWNER AS CONTACTOWNER, ");
			sql.append("MEMBER.CONTACTPHONE AS CONTACTPHONE, ");
			sql.append("MEMBER.INSERTTIME  AS INSERTTIME, ");
			sql.append("TMS_USER.FLAG as MEMSTATUS  ");// 与user表关联取出帐号状态
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" AS MEMBER ");
			sql.append(" LEFT JOIN ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" AS TMS_USER ");
			sql.append(" ON TMS_USER.USERID = MEMBER.USERID ");
			sql.append(" WHERE 1=1");
			if (!StringUtil.isNullOrBlank(memberId)) {
				sql.append(" AND MEMBER.MEMBERID = '" + memberId + "'");
			}
			if ("1".equals(flag)) {
				sql.append(" AND TMS_USER.FLAG = 1");
			} else if ("3".equals(flag)) {
				sql.append(" AND TMS_USER.FLAG = 3");
			} else if ("".equals(flag)) {
				sql.append(" AND TMS_USER.FLAG<>2 ");
			}
			sql.append(" ORDER BY MEMBER.INSERTTIME ASC");
			// 排序方法
			sql.append(getOrderBySql(sortKeyList));

			ps = conn.prepareStatement(getSql(sql.toString()));

			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new MemberDto();
				dto.setMemId(rs.getInt("MEMBERID"));
				dto.setShortName(rs.getString("SHORTNAME"));
				dto.setLegal(rs.getString("LEGAL"));
				dto.setPhone(rs.getString("PHONE"));
				dto.setContactOwner(rs.getString("CONTACTOWNER"));
				dto.setContactPhone(rs.getString("CONTACTPHONE"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				dto.setMemStatus(rs.getInt("MEMSTATUS"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return list;
	}

	/**
	 * 根据会员ID查询会员基本信息
	 */
	@Override
	public MemberDto findMemberInfoByMemId(String memberId) throws TmsDaoException {
		// 开始掉数据
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("MEMBER.ID AS ID, ");// 会员ID
		sql.append("MEMBER.AUCMARGIN AS AUCMARGIN, ");
		sql.append("MEMBER.FRAGILESTICKERFREEPORT AS FRAGILESTICKERFREEPORT, ");
		sql.append("MEMBER.MEMBERID AS MEMBERID, ");// 会员ID
		sql.append("MEMBER.REALNAME AS REALNAME, ");
		sql.append("MEMBER.REALNAMEPY AS REALNAMEPY, ");
		sql.append("MEMBER.INSERTTIME AS INSERTTIME, ");
		sql.append("MEMBER.ADRRESS AS ADRRESS, ");
		sql.append("MEMBER.MEMBERZIPCODE AS MEMBERZIPCODE, ");
		sql.append("MEMBER.ORGNO AS ORGNO, ");
		sql.append("MEMBER.RECOMMEND AS RECOMMEND, ");
		sql.append("MEMBER.BUSINESSNO AS BUSINESSNO, ");
		sql.append("MEMBER.CONTACTOWNER AS CONTACTOWNER, ");
		sql.append("MEMBER.CONTACTPHONE AS CONTACTPHONE, ");
		sql.append("MEMBER.LEGAL AS LEGAL, ");
		sql.append("MEMBER.PHONE AS PHONE, ");
		sql.append("MEMBER.CARDNO AS CARDNO, ");
		sql.append("MEMBER.REGISTEREDCAPITAL AS REGISTEREDCAPITAL, ");
		sql.append("MEMBER.ANNUALTRANSACTIONVOLUME AS ANNUALTRANSACTIONVOLUME, ");
		sql.append("MEMBER.MARGIN AS MARGIN, ");// 账户部分
		sql.append("MEMBER.VIRTUALMARGIN AS VIRTUALMARGIN, ");
		sql.append("MEMBER.USERID AS USERID, ");
		sql.append("MEMBER.ORGPHOTO AS ORGPHOTO, ");
		sql.append("MEMBER.BUSINESSPHOTO AS BUSINESSPHOTO, ");
		sql.append("MEMBER.CARDPHOTO AS CARDPHOTO,   ");
		sql.append("MEMBER.CARPORT AS CARPORT,   ");
		sql.append("MEMBER.FLOORS AS FLOORS,   ");
		sql.append("MEMBER.SETTLEPHONE AS SETTLEPHONE,   ");
		sql.append("MEMBER.CARPORTINSTR AS CARPORTINSTR,   ");
		sql.append("MEMBER.ACCESSCARDID AS ACCESSCARDID,   ");
		sql.append("MEMBER.ACCESSCARDNO AS ACCESSCARDNO,   ");
		sql.append("MEMBER.CARDSCORE AS CARDSCORE,   ");
		sql.append("MEMBER.USEDCARPORT AS USEDCARPORT,   "); // modify by huangwei 20150723
		sql.append("MEMBER.SHORTNAME AS SHORTNAME,   ");
		sql.append("MEMBER.MEMBERZIPCODE AS MEMBERZIPCODE,   ");
		sql.append("MEMBER.INTEGRALRATIO AS INTEGRALRATIO,  ");
		sql.append("MEMBER.IDENTITYRANGE AS IDENTITYRANGE,  ");
		sql.append("MEMBER.TMPIDENTITYRANGE AS TMPIDENTITYRANGE,  ");
		sql.append("MEMBER.CONTRACTTYPE AS CONTRACTTYPE,  ");
		sql.append("MEMBER.CONTRACTTIME AS CONTRACTTIME,  ");
		sql.append("USER.PASSWORD AS PASSWORD,  ");
		sql.append("USER.FLAG AS MEMSTATUS   ");// 与user表关联取出帐号状态
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" AS MEMBER ");
		sql.append(" LEFT JOIN ");
		sql.append(TmsTableNames.TMS_USER);
		sql.append(" AS USER ");
		sql.append(" ON USER.USERID = MEMBER.USERID ");
		sql.append(" WHERE MEMBER.MEMBERID = ? ");
		sql.append(" AND USER.FLAG <> 2 ");

		MemberDto dto = null;
		try {

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, memberId);
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new MemberDto();
				dto.setAucMargin(rs.getInt("AUCMARGIN"));
				dto.setFragileStickerFreePort(rs.getInt("FRAGILESTICKERFREEPORT"));
				dto.setMemId(rs.getInt("MEMBERID"));
				dto.setRealName(rs.getString("REALNAME"));
				dto.setAdrress(rs.getString("ADRRESS"));
				dto.setContractType(rs.getInt("CONTRACTTYPE"));
				dto.setContractTime(StringUtil.isNotBlank(rs.getString("CONTRACTTIME")) ? rs.getTimestamp("CONTRACTTIME") : null);
				dto.setMemberZipCode(rs.getString("MEMBERZIPCODE"));
				dto.setOrgNo(rs.getString("ORGNO"));
				dto.setBusinessNo(rs.getString("BUSINESSNO"));
				dto.setContactOwner(rs.getString("CONTACTOWNER"));
				dto.setRecommend(rs.getString("RECOMMEND"));
				dto.setContactPhone(rs.getString("CONTACTPHONE"));
				dto.setLegal(rs.getString("LEGAL"));
				dto.setPhone(rs.getString("PHONE"));
				dto.setCardNo(rs.getString("CARDNO"));
				dto.setRegisteredCapital(rs.getInt("REGISTEREDCAPITAL"));
				dto.setAnnualTransactionVolume(rs.getInt("ANNUALTRANSACTIONVOLUME"));
				dto.setMargin(rs.getInt("MARGIN"));
				dto.setVirtualMargin(rs.getInt("VIRTUALMARGIN"));
				dto.setUserId(rs.getString("USERID"));
				dto.setOrgPhoto(rs.getString("ORGPHOTO"));
				dto.setBusinessPhoto(rs.getString("BUSINESSPHOTO"));
				dto.setCardPhoto(rs.getString("CARDPHOTO"));
				dto.setFloors(rs.getString("FLOORS"));
				dto.setSettlePhone(rs.getString("SETTLEPHONE"));
				dto.setCarPortInstr(rs.getString("CARPORTINSTR"));
				dto.setCardScore(rs.getInt("CARDSCORE"));
				dto.setAccessCardId(rs.getString("ACCESSCARDID"));
				dto.setAccessCardNo(rs.getString("ACCESSCARDNO"));
				dto.setCarPort(rs.getInt("CARPORT"));
				dto.setUsedCarPort(rs.getInt("USEDCARPORT"));
				dto.setShortName(rs.getString("SHORTNAME"));
				dto.setMemberZipCode(rs.getString("MEMBERZIPCODE"));
				dto.setIntegralRatio(rs.getDouble("INTEGRALRATIO"));
				dto.setRealNamePy(rs.getString("REALNAMEPY"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				dto.setPassword(rs.getString("PASSWORD"));
				dto.setMemStatus(rs.getInt("MEMSTATUS"));
				dto.setIdentityRange(rs.getString("IDENTITYRANGE"));
				dto.setTmpIdentityRange(rs.getString("TMPIDENTITYRANGE"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return dto;
	}

	/**
	 * 
	 * 根据会员ID更新会员账户信息
	 */
	@Override
	public void updateAccountInfoByMemId(MemberDto dto) throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" SET ");
			sql.append(" AUCMARGIN=?,  ");
			sql.append(" MARGIN=?,  ");
			sql.append(" VIRTUALMARGIN=?,  ");
			sql.append(" UPDATETIME=SYSDATE(), ");
			sql.append(" UPDATEUSERID=? ");
			sql.append(" WHERE  ");
			sql.append(" MEMBERID=? ");
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, dto.getAucMargin());
			ps.setInt(2, dto.getMargin());
			ps.setInt(3, dto.getVirtualMargin());
			ps.setLong(4, dto.getUpdateUserId());
			ps.setLong(5, dto.getMemId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 
	 * 根据会员ID更新会员信息
	 */
	@Override
	public void updateBaseMemberInfoByMemId(MemberDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" SET ");
			sql.append("REALNAME=?, ");
			sql.append("SHORTNAME=?, ");
			sql.append("REALNAMEPY=?, ");
			sql.append("CONTACTOWNER=?, ");
			sql.append("CONTACTPHONE=?, ");
			sql.append("LEGAL=?, ");
			sql.append("PHONE=?, ");
			sql.append("REGISTEREDCAPITAL=?, ");
			sql.append("CARDNO=?, ");
			sql.append("SETTLEPHONE=?, ");
			sql.append("ORGNO=?, ");
			sql.append("CARDPHOTO=?, ");
			sql.append("ORGPHOTO=?, ");
			sql.append("BUSINESSPHOTO=?, ");
			sql.append("MEMBERZIPCODE=?, ");
			sql.append("CARPORTINSTR=?, ");
			sql.append("UPDATEUSERID=?, ");
			sql.append("UPDATETIME=SYSDATE() ");
			sql.append(" WHERE  ");
			sql.append(" MEMBERID=? ");

			ps = conn.prepareStatement(sql.toString());

			ps.setString(1, dto.getRealName());
			ps.setString(2, dto.getShortName());
			ps.setString(3, dto.getRealNamePy());
			ps.setString(4, dto.getContactOwner());
			ps.setString(5, dto.getContactPhone());
			ps.setString(6, dto.getLegal());
			ps.setString(7, dto.getPhone());
			ps.setInt(8, dto.getRegisteredCapital());
			ps.setString(9, dto.getCardNo());
			ps.setString(10, dto.getSettlePhone());
			ps.setString(11, dto.getOrgNo());
			ps.setString(12, dto.getCardPhoto());
			ps.setString(13, dto.getOrgPhoto());
			ps.setString(14, dto.getBusinessPhoto());
			ps.setString(15, dto.getMemberZipCode());
			ps.setString(16, dto.getCarPortInstr());
			ps.setInt(17, dto.getUpdateUserId());
			ps.setLong(18, dto.getMemId());

			ps.executeUpdate();

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}

	}

	/**
	 * 
	 * 根据登录者ID查询出会员的基本信息
	 */
	@Override
	public MemberDto findMemberInfoByUserId(String userId) throws TmsDaoException {
		// 开始掉数据
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");// 会员ID
		sql.append("MEMBERID, ");// 会员ID
		sql.append("REALNAME, ");
		sql.append("ADRRESS, ");
		sql.append("MEMBERZIPCODE, ");
		sql.append("ORGNO, ");
		sql.append("BUSINESSNO, ");
		sql.append("CONTACTOWNER, ");
		sql.append("CONTACTPHONE, ");
		sql.append("LEGAL, ");
		sql.append("PHONE, ");
		sql.append("CARDNO, ");
		sql.append("REGISTEREDCAPITAL, ");
		// sql.append("OPERATINGLIFE, ");
		// sql.append("OFFICESPACEAREA, ");
		sql.append("ANNUALTRANSACTIONVOLUME, ");
		sql.append("MARGIN, ");// 账户部分
		sql.append("VIRTUALMARGIN, ");
		sql.append("ORGPHOTO, ");
		sql.append("BUSINESSPHOTO, ");
		sql.append("CARDPHOTO, ");
		sql.append("USERID, ");
		sql.append("CARPORT,  ");
		sql.append("FLOORS, ");
		// sql.append("SETTLENAME,");
		// sql.append("SETTLENO,");
		sql.append("SETTLEPHONE,");
		sql.append("CARPORTINSTR, ");
		sql.append("SHORTNAME");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append("   WHERE USERID =? ");

		MemberDto dto = null;
		try {

			ps = conn.prepareStatement(sql.toString());

			ps.setString(1, userId);

			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new MemberDto();

				dto.setMemId(rs.getInt("MEMBERID"));
				dto.setRealName(rs.getString("REALNAME"));
				dto.setAdrress(rs.getString("ADRRESS"));
				dto.setMemberZipCode(rs.getString("MEMBERZIPCODE"));
				dto.setOrgNo(rs.getString("ORGNO"));
				dto.setBusinessNo(rs.getString("BUSINESSNO"));
				dto.setContactOwner(rs.getString("CONTACTOWNER"));
				dto.setContactPhone(rs.getString("CONTACTPHONE"));
				dto.setLegal(rs.getString("LEGAL"));
				dto.setPhone(rs.getString("PHONE"));
				dto.setCardNo(rs.getString("CARDNO"));
				dto.setRegisteredCapital(rs.getInt("REGISTEREDCAPITAL"));
				// dto.setOperatingLife(rs.getInt("OPERATINGLIFE"));
				// dto.setOfficeSpaceArea(rs.getInt("OFFICESPACEAREA"));
				dto.setAnnualTransactionVolume(rs.getInt("ANNUALTRANSACTIONVOLUME"));
				dto.setMargin(rs.getInt("MARGIN"));
				dto.setVirtualMargin(rs.getInt("VIRTUALMARGIN"));
				dto.setUserId(rs.getString("USERID"));
				dto.setOrgPhoto(rs.getString("ORGPHOTO"));
				dto.setBusinessPhoto(rs.getString("BUSINESSPHOTO"));
				dto.setCardPhoto(rs.getString("CARDPHOTO"));
				dto.setSpacesNo(rs.getInt("CARPORT"));
				dto.setFloors(rs.getString("FLOORS"));
				// dto.setSettleName(rs.getString("SETTLENAME"));
				// dto.setSettleNo(rs.getString("SETTLENO"));
				dto.setSettlePhone(rs.getString("SETTLEPHONE"));
				dto.setCarPortInstr(rs.getString("CARPORTINSTR"));
				dto.setShortName(rs.getString("SHORTNAME"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return dto;
	}

	/**
	 * 
	 * 会员信息修改删除列表页list获取
	 * 
	 * @throws TmsDaoException
	 */
	@Override
	public List<MemberDto> sendSMSMemberList() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		MemberDto dto = null;
		List<MemberDto> list = new ArrayList<MemberDto>();

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("MEMBER.MEMBERID  AS MEMBERID 	,");
			sql.append("MEMBER.REALNAME  AS REALNAME 	,");
			sql.append("MEMBER.USERID AS USERID,     ");
			sql.append("MEMBER.ADRRESS AS ADRRESS,     ");
			sql.append("MEMBER.CONTACTOWNER AS CONTACTOWNER,     ");
			sql.append("MEMBER.CONTACTPHONE AS CONTACTPHONE,     ");
			sql.append("MEMBER.MARGIN  AS MARGIN ,   ");
			sql.append("MEMBER.VIRTUALMARGIN AS VIRTUALMARGIN,     ");
			sql.append("USER.FLAG as MEMSTATUS     ");// 与user表关联取出帐号状态
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" AS MEMBER    ");
			sql.append(" LEFT JOIN    ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" AS USER      ");
			sql.append(" ON USER.USERID = MEMBER.USERID    ");
			sql.append(" WHERE USER.FLAG<>2  ");

			ps = conn.prepareStatement(sql.toString());

			System.out.println("-------------------------------------");

			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new MemberDto();
				dto.setMemId(rs.getInt("MEMBERID"));
				dto.setRealName(rs.getString("REALNAME"));
				dto.setUserId(rs.getString("USERID"));
				dto.setAdrress(rs.getString("ADRRESS"));
				dto.setContactOwner(rs.getString("CONTACTOWNER"));
				dto.setContactPhone(rs.getString("CONTACTPHONE"));
				dto.setMargin(rs.getInt("MARGIN"));
				dto.setVirtualMargin(rs.getInt("VIRTUALMARGIN"));
				dto.setMemStatus(rs.getInt("MEMSTATUS"));

				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return list;
	}

	@Override
	public MemberDto getMember(String memberId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		MemberDto dto = new MemberDto();
		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("REALNAME,");
			sql.append("ACCESSCARDID,");
			sql.append("CARPORT,");
			sql.append("USEDCARPORT,");
			sql.append("USERID, ");
			sql.append("MEMBERID, ");
			sql.append("SHORTNAME, ");
			sql.append("RECOMMEND, ");
			sql.append("CONTACTOWNER, ");
			sql.append("CONTACTPHONE, ");
			sql.append("ACCESSCARDNO  ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" WHERE ");
			sql.append("MEMBERID= " + memberId);
			ps = conn.prepareStatement(sql.toString());
			// 执行sql
			rs = ps.executeQuery();
			while (rs.next()) {
				dto.setMemName(rs.getString("REALNAME"));
				dto.setRealName(rs.getString("REALNAME"));
				dto.setAccessCardId(rs.getString("ACCESSCARDID"));
				dto.setCarPort(rs.getInt("CARPORT"));
				dto.setUsedCarPort(rs.getInt("USEDCARPORT"));
				dto.setUserId(rs.getString("USERID"));
				dto.setMemId(rs.getInt("MEMBERID"));
				dto.setShortName(rs.getString("SHORTNAME"));
				dto.setRecommend(rs.getString("RECOMMEND"));
				dto.setAccessCardNo(rs.getString("ACCESSCARDNO"));
				dto.setContactOwner(rs.getString("CONTACTOWNER"));
				dto.setContactPhone(rs.getString("CONTACTPHONE"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
		return dto;
	}

	/**
	 * 
	 * 根据会员ID或许更新时间
	 */
	@Override
	public int findUpdateTimeByMemberId(String memberId, String inTime) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int updateTime = 0;

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("UPDATETIME");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" WHERE ");
			sql.append("MEMBERID=? ");
			sql.append("AND UPDATETIME > '" + inTime + "' ");

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, memberId);
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				if (rs.getString("UPDATETIME") == null || "".equals(rs.getString("UPDATETIME"))) {
					updateTime = 1;// 打
				} else {
					updateTime = 0;// 不打
				}

			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return updateTime;
	}

	/**
	 * 根据会员ID查询会员信息是否存在
	 */
	@Override
	public int findMemberInfoByMemberId(String memberId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int updateTime = 0;

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("UPDATETIME");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" WHERE ");
			sql.append("MEMBERID=? ");

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, memberId);
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				if (rs.getString("UPDATETIME") == null || "".equals(rs.getString("UPDATETIME"))) {
					updateTime = 1;// 打
				} else {
					updateTime = 0;// 不打
				}

			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return updateTime;
	}

	/**
	 * 根据MemberId载入登记楼层
	 */
	@Override
	public MemberDto loadCarScore(String memberId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		MemberDto dto = new MemberDto();
		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("MEMBER.FLOORS,");
			sql.append("MEMBER.CARPORT,");
			// add by huangheli at 20150803 start
			sql.append("MEMBER.USEDCARPORT,");
			// add by huangheli at 20150803 end
			// add by huangheli at 20151012 start
			sql.append("MEMBER.INTEGRALRATIO,");
			// add by huangheli at 20151012 end
			sql.append("MEMBER.FRAGILESTICKERFREEPORT,");
			sql.append("MEMBER.CARPORTINSTR,");
			sql.append("MEMBER.CONTACTOWNER,");
			sql.append("MEMBER.CONTACTPHONE,");
			sql.append("MEMBER.MEMBERID,");
			sql.append("MEMBER.CARDSCORE, ");
			sql.append("MEMBER.SHORTNAME, ");
			sql.append("MEMBER.ANNUALTRANSACTIONVOLUME, ");
			sql.append("MEMBER.VIRTUALMARGIN, ");
			sql.append("USER.PASSWORD ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" MEMBER ");
			sql.append(" LEFT JOIN ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" USER ");
			sql.append(" ON MEMBER.USERID = USER.USERID ");
			sql.append(" WHERE ");
			sql.append("MEMBERID=? ");

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, memberId);
			// 执行sql
			rs = ps.executeQuery();
			while (rs.next()) {
				dto = new MemberDto();
				dto.setFloors(rs.getString("FLOORS"));
				dto.setCarPort(rs.getInt("CARPORT"));
				dto.setPassword(rs.getString("PASSWORD"));
				dto.setUsedCarPort(rs.getInt("USEDCARPORT"));
				dto.setIntegralRatio(rs.getDouble("INTEGRALRATIO"));
				dto.setCarPortInstr(rs.getString("CARPORTINSTR"));
				dto.setContactOwner(rs.getString("CONTACTOWNER"));
				dto.setContactPhone(rs.getString("CONTACTPHONE"));
				dto.setMemId(rs.getInt("MEMBERID"));
				dto.setMemName(rs.getString("SHORTNAME"));
				dto.setCardScore(rs.getInt("CARDSCORE"));
				dto.setAnnualTransactionVolume(rs.getInt("ANNUALTRANSACTIONVOLUME"));
				dto.setFragileStickerFreePort(rs.getInt("FRAGILESTICKERFREEPORT"));
				dto.setVirtualMargin(rs.getInt("VIRTUALMARGIN"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return dto;
	}

	@Override
	public MemberDto getIntegralManagementInfo(String accId) throws TmsDaoException {
		if (StringUtil.isNullOrBlank(accId))
			return null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		MemberDto dto = null;
		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("MEMBER.MEMBERID AS MEMBERID,");
			sql.append("MEMBER.ACCESSCARDID AS ACCESSCARDID,");
			sql.append("MEMBER.REALNAME AS REALNAME,");
			sql.append("MEMBER.LEGAL AS LEGAL,");
			sql.append("MEMBER.PHONE AS PHONE,");
			sql.append("MEMBER.CARDNO AS CARDNO,");
			sql.append("MEMBER.CARDPHOTO AS CARDPHOTO,");
			sql.append("MEMBER.CARDSCORE AS CARDSCORE,");
			sql.append("MEMBER.INTEGRALRATIO AS INTEGRALRATIO,");
			sql.append("MEMBER.VIRTUALMARGIN AS VIRTUALMARGIN,     ");
			sql.append("MEMBER.REALMARGIN AS REALMARGIN,     ");
			sql.append("MEMBER.GIFTMARGIN AS GIFTMARGIN,     ");
			sql.append("USER.FLAG AS USERID ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" MEMBER ");
			sql.append(" INNER JOIN ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" USER ");
			sql.append(" ON MEMBER.USERID = USER.USERID ");
			sql.append(" WHERE ");
			sql.append(" MEMBER.ACCESSCARDNO=? ");

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, accId);
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new MemberDto();
				dto.setMemId(rs.getInt("MEMBERID"));
				dto.setRealName(rs.getString("REALNAME"));
				dto.setLegal(rs.getString("LEGAL"));
				dto.setPhone(rs.getString("PHONE"));
				dto.setCardNo(rs.getString("CARDNO"));
				dto.setCardPhoto(rs.getString("CARDPHOTO"));
				dto.setUserId(rs.getString("USERID"));
				dto.setAccessCardId(rs.getString("ACCESSCARDID"));
				dto.setVirtualMargin(rs.getInt("VIRTUALMARGIN"));
				dto.setRealMargin(rs.getInt("REALMARGIN"));
				dto.setGiftMargin(rs.getInt("GIFTMARGIN"));
				dto.setCardScore(rs.getInt("CARDSCORE"));
				// add by huangheli at 20151012 start
				dto.setIntegralRatio(rs.getDouble("INTEGRALRATIO"));
				// add by huangheli at 20151012 end
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return dto;
	}

	@Override
	public void updateIntegralcard(MemberDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" SET ");
		sql.append("ACCESSCARDID=?, ");
		sql.append("ACCESSCARDNO=?, ");
		sql.append("UPDATEUSERID=?, ");
		sql.append("UPDATETIME=SYSDATE() ");
		sql.append(" WHERE  ");
		sql.append(" MEMBERID=? ");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getAccessCardId());
			ps.setString(2, dto.getAccessCardNo());
			ps.setInt(3, dto.getUpdateUserId());
			ps.setString(4, dto.getMemName());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}

	}

	@Override
	public void updateMarginInfo(MemberDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" SET ");
		sql.append("VIRTUALMARGIN=?, ");
		sql.append("REALMARGIN=?, ");
		sql.append("GIFTMARGIN=?, ");
		sql.append("UPDATEUSERID=?, ");
		sql.append("UPDATETIME=SYSDATE() ");
		sql.append(" WHERE  ");
		sql.append(" ACCESSCARDNO=? ");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, dto.getVirtualMargin());
			ps.setInt(2, dto.getRealMargin());
			ps.setInt(3, dto.getGiftMargin());
			ps.setInt(4, dto.getUpdateUserId());
			ps.setString(5, dto.getAccessCardNo());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
	}

	@Override
	public void updateIntegralCardScore(MemberDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" SET ");
		sql.append("CARDSCORE=?, ");
		sql.append("UPDATEUSERID=?, ");
		sql.append("UPDATETIME=SYSDATE() ");
		sql.append(" WHERE  ");
		sql.append(" MEMBERID=? ");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, dto.getCardScore());
			ps.setInt(2, dto.getUpdateUserId());
			ps.setInt(3, dto.getMemId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
	}

	@Override
	public int findMemberByAccessCardId(String accessCardId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int flag = 1;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append("MEMBERID");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" WHERE ");
		sql.append("ACCESSCARDID=? ");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, accessCardId);
			rs = ps.executeQuery();
			while (rs.next()) {
				flag = 2;
			}

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return flag;
	}

	@Override
	public int findMemberByAccessCardNo(String accessCardNo) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int flag = 1;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append("MEMBERID");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" WHERE ");
		sql.append("ACCESSCARDNO=? ");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, accessCardNo);
			rs = ps.executeQuery();
			while (rs.next()) {
				flag = 2;
			}

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return flag;
	}

	@Override
	public void updateUsedCarPort(MemberDto dto, int increment) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" SET ");
		sql.append("USEDCARPORT=USEDCARPORT+" + increment + ", ");
		sql.append("UPDATEUSERID=" + dto.getUpdateUserId() + ", ");
		sql.append("UPDATETIME=SYSDATE() ");
		sql.append(" WHERE  ");
		sql.append(" MEMBERID= " + dto.getMemId());

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
	}

	@Override
	public void lockMemberForUpdate(int memberId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ID FROM ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" WHERE ID = ?");
		sql.append(" FOR UPDATE");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, memberId);
			ps.executeQuery();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public MemberDto findTransferDataSyn40000(String memberId) throws TmsDaoException {
		// 开始掉数据
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");// 会员ID
		sql.append("MEMBERID, ");// 会员ID
		sql.append("REALNAME, ");
		sql.append("ADRRESS, ");
		sql.append("MEMBERZIPCODE, ");
		sql.append("ORGNO, ");
		sql.append("BUSINESSNO, ");
		sql.append("CONTACTOWNER, ");
		sql.append("CONTACTPHONE, ");
		sql.append("LEGAL, ");
		sql.append("PHONE, ");
		sql.append("CARDNO, ");
		sql.append("REGISTEREDCAPITAL, ");
		sql.append("OPERATINGLIFE, ");
		sql.append("OFFICESPACEAREA, ");
		sql.append("ANNUALTRANSACTIONVOLUME, ");
		sql.append("MARGIN, ");// 账户部分
		sql.append("VIRTUALMARGIN, ");
		sql.append("USERID, ");
		sql.append("ORGPHOTO, ");
		sql.append("BUSINESSPHOTO, ");
		sql.append("CARDPHOTO,   ");
		sql.append("CARPORT,   ");
		sql.append("FLOORS,   ");
		sql.append("SETTLENAME,   ");
		sql.append("SETTLENO,   ");
		sql.append("SETTLEPHONE,   ");
		sql.append("CARPORTINSTR,   ");
		sql.append("ACCESSCARDID,   ");
		sql.append("ACCESSCARDNO,   ");
		sql.append("CARDSCORE,   ");
		sql.append("USEDCARPORT,   ");
		sql.append("realMargin   ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append("   WHERE MEMBERID = ? ");

		MemberDto dto = null;
		try {

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, memberId);
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new MemberDto();
				dto.setMemId(rs.getInt("MEMBERID"));
				dto.setRealName(rs.getString("REALNAME"));
				dto.setAdrress(rs.getString("ADRRESS"));
				dto.setMemberZipCode(rs.getString("MEMBERZIPCODE"));
				dto.setOrgNo(rs.getString("ORGNO"));
				dto.setBusinessNo(rs.getString("BUSINESSNO"));
				dto.setContactOwner(rs.getString("CONTACTOWNER"));
				dto.setContactPhone(rs.getString("CONTACTPHONE"));
				dto.setLegal(rs.getString("LEGAL"));
				dto.setPhone(rs.getString("PHONE"));
				dto.setCardNo(rs.getString("CARDNO"));
				dto.setRegisteredCapital(rs.getInt("REGISTEREDCAPITAL"));
				dto.setOperatingLife(rs.getInt("OPERATINGLIFE"));
				dto.setOfficeSpaceArea(rs.getInt("OFFICESPACEAREA"));
				dto.setAnnualTransactionVolume(rs.getInt("ANNUALTRANSACTIONVOLUME"));
				dto.setMargin(rs.getInt("MARGIN"));
				dto.setVirtualMargin(rs.getInt("VIRTUALMARGIN"));
				dto.setUserId(rs.getString("USERID"));
				dto.setOrgPhoto(rs.getString("ORGPHOTO"));
				dto.setBusinessPhoto(rs.getString("BUSINESSPHOTO"));
				dto.setCardPhoto(rs.getString("CARDPHOTO"));
				dto.setFloors(rs.getString("FLOORS"));
				dto.setSettleName(rs.getString("SETTLENAME"));
				dto.setSettleNo(rs.getString("SETTLENO"));
				dto.setSettlePhone(rs.getString("SETTLEPHONE"));
				dto.setCarPortInstr(rs.getString("CARPORTINSTR"));
				dto.setCardScore(rs.getInt("CARDSCORE"));
				dto.setAccessCardId(rs.getString("ACCESSCARDID"));
				dto.setAccessCardNo(rs.getString("ACCESSCARDNO"));
				dto.setCarPort(rs.getInt("CARPORT"));
				dto.setUsedCarPort(rs.getInt("USEDCARPORT"));
				dto.setRealMargin(rs.getInt("realMargin"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return dto;
	}

	/**
	 * 查找商户名称
	 * 
	 * @param memberName
	 * @param flag
	 * @throws TmsDaoException
	 */
	public List<MemberDto> findMemberNameList(String memberName, int flag) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<MemberDto> dtoList = new ArrayList<MemberDto>();
		;
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" MEMBER.MEMBERID AS MEMBERID, ");
		sql.append(" MEMBER.SHORTNAME AS SHORTNAME, ");
		sql.append(" MEMBER.REALNAMEPY AS REALNAMEPY ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" AS MEMBER  ");
		sql.append(" LEFT JOIN  ");// 和user表关联
		sql.append(TmsTableNames.TMS_USER);
		sql.append(" AS USER  ");
		sql.append(" ON USER.USERID=MEMBER.USERID ");
		sql.append(" WHERE MEMBER.REALNAMEPY like '%");
		sql.append(memberName);
		sql.append("%'");
		if (flag == 1) {
			sql.append(" AND USER.FLAG = 1");
		} else if (flag == 2) {
			sql.append(" AND (USER.FLAG = 1 OR USER.FLAG = 3)");
		}
		sql.append(" ORDER BY MEMBER.REALNAMEPY ASC");
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				MemberDto dto = new MemberDto();
				dto.setMemId(rs.getInt("MEMBERID"));
				dto.setShortName(rs.getString("SHORTNAME"));
				dto.setRealNamePy(rs.getString("REALNAMEPY"));
				dtoList.add(dto);
			}
			return dtoList;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 判断商户名称是否重复
	 */
	@Override
	public boolean isRepeatMemberName(String memberName, int type) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean result = false;

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append(" MEMBER.MEMBERID AS MEMBERID ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" AS MEMBER ");
			sql.append(" LEFT JOIN ");// 和user表关联
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" AS USER ");
			sql.append(" ON USER.USERID=MEMBER.USERID ");
			sql.append(" WHERE ");
			if (type == 1) {
				sql.append(" MEMBER.REALNAME=? ");
			} else if (type == 2) {
				sql.append(" MEMBER.SHORTNAME=? ");
			}
			sql.append(" AND (USER.FLAG = 1 OR USER.FLAG = 3)");

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, memberName);
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				result = true;
			}
			return result;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
	}

	/**
	 * 更新合同编号List
	 */
	public void updateMemberBusinessNoInfo(MemberDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" SET ");
			sql.append(" BUSINESSNO=?,  ");
			sql.append(" UPDATETIME=SYSDATE(), ");
			sql.append(" UPDATEUSERID=?, ");
			sql.append(" FLOORS=? ");
			sql.append(" WHERE  ");
			sql.append(" MEMBERID=? ");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getBusinessNo());
			ps.setInt(2, dto.getUpdateUserId());
			ps.setString(3, dto.getFloors());
			ps.setLong(4, dto.getMemId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 更新member信息
	 */
	public void updateMemberInfo(MemberDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append(" SET ");
			sql.append(" CARPORT=?, ");
			sql.append(" ANNUALTRANSACTIONVOLUME=?, ");
			sql.append(" FRAGILESTICKERFREEPORT=?, ");
			sql.append(" RECOMMEND=?, ");
			sql.append(" UPDATETIME=SYSDATE(), ");
			sql.append(" UPDATEUSERID=?, ");
			sql.append(" IDENTITYRANGE=?, ");
			sql.append(" TMPIDENTITYRANGE=?, ");
			sql.append(" CONTRACTTYPE=?, ");
			sql.append(" CONTRACTTIME=? ");
			sql.append(" WHERE  ");
			sql.append(" MEMBERID=? ");
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, dto.getCarPort());
			ps.setInt(2, dto.getAnnualTransactionVolume());
			ps.setInt(3, dto.getFragileStickerFreePort());
			ps.setInt(4, Integer.valueOf(dto.getRecommend()));
			ps.setInt(5, dto.getUpdateUserId());
			ps.setString(6, dto.getIdentityRange());
			ps.setString(7, dto.getTmpIdentityRange());
			ps.setInt(8, dto.getContractType());
			ps.setTimestamp(9, dto.getContractTime());
			ps.setLong(10, dto.getMemId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	public void updateMarginInfoByAccessCardNo(MemberDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" SET ");
		sql.append("VIRTUALMARGIN=?, ");
		sql.append("REALMARGIN=?, ");
		sql.append("UPDATEUSERID=?, ");
		sql.append("recharge=?,");
		sql.append("UPDATETIME=SYSDATE(), ");
		sql.append("rechargeTime=SYSDATE() ");
		sql.append(" WHERE  ");
		sql.append(" ACCESSCARDNO=? ");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, dto.getVirtualMargin());
			ps.setInt(2, dto.getRealMargin());
			ps.setInt(3, dto.getUpdateUserId());
			ps.setInt(4, dto.getRecharge());
			ps.setString(5, dto.getAccessCardNo());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
	}

	public void updateMemberVirtualMargin(String memberId, int virtualMargin) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" SET ");
		sql.append(" VIRTUALMARGIN=? ");
		sql.append(" WHERE  ");
		sql.append(" MEMBERID=? ");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, virtualMargin);
			ps.setString(2, memberId);
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
	}

	@Override
	public List<MemberDto> findRecommendMember(int recommend) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		MemberDto dto = null;
		List<MemberDto> list = new ArrayList<MemberDto>();
		try {
			conn = super.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ID, ");// 会员ID
			sql.append("MEMBERID, ");// 会员ID
			sql.append("REALNAME, ");
			sql.append("ADRRESS, ");
			sql.append("MEMBERZIPCODE, ");
			sql.append("ORGNO, ");
			sql.append("BUSINESSNO, ");
			sql.append("CONTACTOWNER, ");
			sql.append("CONTACTPHONE, ");
			sql.append("LEGAL, ");
			sql.append("PHONE, ");
			sql.append("CARDNO, ");
			sql.append("REGISTEREDCAPITAL, ");
			sql.append("ANNUALTRANSACTIONVOLUME, ");
			sql.append("MARGIN, ");// 账户部分
			sql.append("VIRTUALMARGIN, ");
			sql.append("ORGPHOTO, ");
			sql.append("BUSINESSPHOTO, ");
			sql.append("CARDPHOTO, ");
			sql.append("USERID, ");
			sql.append("CARPORT,  ");
			sql.append("FLOORS, ");
			sql.append("SETTLEPHONE,");
			sql.append("MEMBERLOGO, ");
			sql.append("MEMBERLOCOLLOGO, ");
			sql.append("CARPORTINSTR, ");
			sql.append("SHORTNAME");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MEMBER);
			sql.append("   WHERE RECOMMEND =? ORDER BY UPDATETIME DESC LIMIT 10 ");
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, recommend);
			// 执行sql
			rs = ps.executeQuery();
			while (rs.next()) {
				dto = new MemberDto();
				dto.setMemId(rs.getInt("MEMBERID"));
				dto.setRealName(rs.getString("REALNAME"));
				dto.setAdrress(rs.getString("ADRRESS"));
				dto.setMemberZipCode(rs.getString("MEMBERZIPCODE"));
				dto.setOrgNo(rs.getString("ORGNO"));
				dto.setBusinessNo(rs.getString("BUSINESSNO"));
				dto.setContactOwner(rs.getString("CONTACTOWNER"));
				dto.setContactPhone(rs.getString("CONTACTPHONE"));
				dto.setLegal(rs.getString("LEGAL"));
				dto.setPhone(rs.getString("PHONE"));
				dto.setCardNo(rs.getString("CARDNO"));
				dto.setRegisteredCapital(rs.getInt("REGISTEREDCAPITAL"));
				dto.setAnnualTransactionVolume(rs.getInt("ANNUALTRANSACTIONVOLUME"));
				dto.setMargin(rs.getInt("MARGIN"));
				dto.setVirtualMargin(rs.getInt("VIRTUALMARGIN"));
				dto.setUserId(rs.getString("USERID"));
				dto.setOrgPhoto(rs.getString("ORGPHOTO"));
				dto.setBusinessPhoto(rs.getString("BUSINESSPHOTO"));
				dto.setCardPhoto(rs.getString("CARDPHOTO"));
				dto.setSpacesNo(rs.getInt("CARPORT"));
				dto.setFloors(rs.getString("FLOORS"));
				dto.setSettlePhone(rs.getString("SETTLEPHONE"));
				dto.setCarPortInstr(rs.getString("CARPORTINSTR"));
				dto.setMemberLogo(rs.getString("MEMBERLOGO"));
				dto.setMemberLocolLogo(rs.getString("MEMBERLOCOLLOGO"));
				dto.setShortName(rs.getString("SHORTNAME"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return list;
	}

	
	
	@Override
	public MemberRentalLocationInfo findMemberLocationInfoCount(String serials) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		//
		StringBuffer param = new StringBuffer("");
		if(StringUtil.isNotBlank(serials)) param.append("'").append(serials.replace(",", "','")).append("'");
		//
		MemberRentalLocationInfo r = new MemberRentalLocationInfo();
		StringBuffer sql = new StringBuffer(" SELECT SUM(B.CARPORT) AS CARPORT,SUM(B.ANNUALTRANSACTIONVOLUME) AS ANNUALTRANSACTIONVOLUME ");
		sql.append(" FROM ");
		sql.append(" "+TmsTableNames.TMS_CONTRACT_INFO+" AS A, ");
		sql.append(" "+TmsTableNames.TMS_RENTALLOCATION_INFO+" AS B ");
		sql.append(" WHERE ");
		sql.append(" DATE(A.STARTDATE) <= DATE(NOW()) ");
		sql.append(" AND A.LOCATIONSERIAL = B.SERIAL ");
		sql.append(" AND A.SERIAL IN ("+param+") ");
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			if (rs.next()) {
				r.setUsedAnnualTransactionVolume(rs.getString("ANNUALTRANSACTIONVOLUME"));
				r.setUsedCarPort(rs.getString("CARPORT"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
		return r;
	}

	@Override
	public MemberRentalLocationInfo findMemberRentalLocationInfoCount(String serials) throws TmsDaoException {
		//
		StringBuffer param = new StringBuffer("");
		if(StringUtil.isNotBlank(serials)) param.append("'").append(serials.replace(",", "','")).append("'");
		//
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		MemberRentalLocationInfo r = new MemberRentalLocationInfo();
		StringBuffer sql = new StringBuffer(" SELECT SUM(B.CARPORT) AS CARPORT,SUM(B.ANNUALTRANSACTIONVOLUME) AS ANNUALTRANSACTIONVOLUME ");
		sql.append(" FROM ");
		sql.append(" "+TmsTableNames.TMS_CONTRACT_INFO+" AS A, ");
		sql.append(" "+TmsTableNames.TMS_RENTALLOCATION_INFO+" AS B ");
		sql.append(" WHERE ");
		sql.append(" DATE(A.STARTDATE) > DATE(NOW()) ");
		sql.append(" AND A.LOCATIONSERIAL = B.SERIAL ");
		sql.append(" AND A.SERIAL IN ("+param+") ");
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			if (rs.next()) {
				r.setUnusedAnnualTransactionVolume(rs.getString("ANNUALTRANSACTIONVOLUME"));
				r.setUnusedCarPort(rs.getString("CARPORT"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
		return r;
	}

	@Override
	public int findRegistedCount(String memberId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		StringBuffer sql = new StringBuffer("SELECT COUNT(*) COUNT FROM ");
		sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
		sql.append(" WHERE MEMBERID = ? ");
		sql.append(" AND REGISTERSTATUS IN (1,2,6) ");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, memberId);
			rs = ps.executeQuery();
			if (rs.next()) {
				count = rs.getInt("COUNT");
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
		return count;
	}

	@Override
	public void updateMemberVirtualMarginByUserId(String userId, int virtualMargin) throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" SET ");
		sql.append(" VIRTUALMARGIN=? ");
		sql.append(" WHERE  ");
		sql.append(" MEMBERID = (SELECT TMP.MEMBERID FROM ( SELECT TA.MEMBERID FROM ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" TA , ");
		sql.append(TmsTableNames.TMS_USER);
		sql.append(" TU WHERE TU.USERID= ? AND TA.USERID = TU.USERID) AS TMP) ");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, virtualMargin);
			ps.setString(2, userId);
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}

	}
}